package com.zizo.autohome.sta.competition.level;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.zizo.util.ConnectionPool;

public class CompLevelDao implements Runnable {
	private Connection conn;
	private int cid;
	private String cname;
	private int seg;
	private int level;

	public void createtable() {
		String sql = "DROP TABLE IF EXISTS biz_competition_level_temp;";
		String sql1 = "create table biz_competition_level_temp like biz_competition_level";
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void droptable() {
		String sql = "DROP TABLE IF EXISTS biz_competition_level;";
		String sql1 = "ALTER TABLE biz_competition_level_temp RENAME biz_competition_level;";
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public CompLevelDao(Connection conn, int cid, String cname, int seg, int level) {
		super();
		this.conn = conn;
		this.cid = cid;
		this.cname = cname;
		this.seg = seg;
		this.level = level;
	}

	public List<CompLevelDao> getBrand() {
		List<CompLevelDao> list = new ArrayList<>();
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT brand_id,brand_name,brand_segment,brand_level FROM carvoice_biz.biz_brand";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int bid = rs.getInt(1);
				String bname = rs.getString(2);
				int seg = rs.getInt(3);
				int level = rs.getInt(4);
				CompLevelDao c = new CompLevelDao(conn, bid, bname, seg, level);
				list.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public void update() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT biz_brand.brand_id,brand_name FROM carvoice_biz.biz_brand WHERE brand_segment=" + seg
				+ " AND brand_level=" + level + " AND brand_id !=" + cid;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int brand_id = rs.getInt(1);
				String brand_name = rs.getString(2);
				CompLevel com = new CompLevel(cid, brand_id, cname, brand_name, seg, level);
				String s1 = getPriceDiff(cid);
				String s2 = getPriceDiff(brand_id);
				double diff = 100000000;
				if (s1 != null && s2 != null) {
					double p1 = Double.parseDouble(s1.split("%%")[0]);
					double p2 = Double.parseDouble(s2.split("%%")[0]);
					String t1 = s1.split("%%")[1];
					String t2 = s2.split("%%")[1];
					boolean res = getDateDiff(t1, t2);
					if (res) {
						diff = Math.abs(p1 - p2);
					}
				}
				insert(com, diff);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String getPriceDiff(int brand_id) {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT price_lcj,price_btime FROM autohome_other.other_price WHERE price_cid=(SELECT t.brand_autohome FROM carvoice_biz.biz_brand AS t WHERE brand_id="
				+ brand_id + ") AND price_istrue=1 ORDER BY price_btime DESC LIMIT 0,1";
		String res = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				double lcj = -1;
				lcj = rs.getDouble(1);
				String time = rs.getString(2);
				if (lcj == -1) {
					return null;
				} else {
					res = lcj + "%%" + time;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public boolean getDateDiff(String s1, String s2) {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT TIMESTAMPDIFF(MONTH,'" + s1 + "1','" + s2 + "')";
		boolean res = false;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = Math.abs(rs.getInt(1)) <= 12 ? true : false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public void insert(CompLevel com, double diff) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO carvoice_biz.biz_competition_level_temp(cl_cid1,cl_cname1,cl_cid2,cl_cname2,cl_segment,cl_level,cl_diff) values(?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, com.getCid1());
			ps.setString(2, com.getCname1());
			ps.setInt(3, com.getCid2());
			ps.setString(4, com.getCname2());
			ps.setInt(5, com.getSegment());
			ps.setInt(6, com.getLevel());
			ps.setDouble(7, diff);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void run() {
		update();
		System.out.println(cid);
	}

	public CompLevelDao(Connection conn) {
		super();
		this.conn = conn;
	}

	public static void main(String[] args) {
		Connection conn = ConnectionPool.getConnection("carvoice_biz", "localhost");
		CompLevelDao dao = new CompLevelDao(conn);
		dao.createtable();
		List<CompLevelDao> com = dao.getBrand();
		ExecutorService fix = Executors.newFixedThreadPool(20);
		for (CompLevelDao d : com) {
			fix.execute(d);
		}
		fix.shutdown();
		while (true) {
			if (fix.isTerminated()) {
				dao.droptable();
				break;
			}
			try {
				Thread.sleep(1000);
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
		}
	}
}
